﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

public class GuideArrangeCtrl
{
    #region 获取导游安排数据

    /// <summary>
    /// 检测导游是否存在已经安排的信息
    /// </summary>
    /// <param name="ciceroniId"></param>
    /// <returns></returns>
    public static bool CheckExistGuideArrangeInfo(string ciceroniId)
    {
        Database db = DatabaseFactory.CreateDatabase();
        DbCommand dbCommand;

        dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT CiceroniID FROM cic_GuideArrange WHERE CiceroniID='{0}'", ciceroniId.Trim()));

        object obj = db.ExecuteScalar(dbCommand);

        return obj != null;
    }
    /// <summary>
    /// 获取指定团所安排的导游标识和导游姓名
    /// </summary>
    /// <param name="tourgroupId"></param>
    /// <returns></returns>
    public static DataTable GetCiceroniIDAndNameFromGuideArrange(string tourgroupId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT [CiceroniID], [CiceroniName] FROM cic_GuideArrange WHERE TourgroupID='{0}'", tourgroupId);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    /// 获取指定团的导游安排数据信息
    /// </summary>
    /// <param name="tourgroupId"></param>
    /// <returns></returns>
    public static DataTable GetGuideArrangeData(string tourgroupId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT g.*,c.Phone FROM cic_GuideArrange g,bas_Ciceroni c WHERE TourgroupID='{0}'AND g.CiceroniID = c.CiceroniID", tourgroupId);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    /// <summary>
    /// 获取指定团的导游信息
    /// </summary>
    /// <param name="tourgroupId"></param>
    /// <returns></returns>
    public static DataTable GetGuideInfoForPrint(string tourgroupId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT cic_GuideArrange.StartDate,bas_Ciceroni.CiceroniName,bas_Ciceroni.Phone FROM cic_GuideArrange,bas_Ciceroni WHERE TourgroupID='{0}' and cic_GuideArrange.CiceroniID = bas_Ciceroni.CiceroniID", tourgroupId);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    public static DataTable GetGuideArrangeData(DateTime _StartDate, DateTime _EndDate)
    {
        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT [GuideArrangeID], [TourgroupID], [StartDate],[Calendars], [EndDate], [CiceroniID], [CiceroniName],
                                                [Notified], [IsAccompany],[HasEvent], [Remark]  FROM cic_GuideArrange 
                                                 WHERE  (datediff(minute,StartDate,'{0}')>=0  
                                                 AND datediff(minute,EndDate,'{0}')<=0) 
                                                 OR (datediff(minute,StartDate,'{1}')>=0  
                                                 AND datediff(minute,EndDate,'{1}')<=0) 
                                                 OR (datediff(minute,StartDate,'{0}')<=0  
                                                 AND datediff(minute,EndDate,'{1}')>=0)",
                                             _StartDate.ToString("yyyy-MM-dd HH:mm"),
                                             _EndDate.ToString("yyyy-MM-dd HH:mm"));
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
        {
            DateTime start, end;
            //循环修改时间段信息，用于返回后着色GridView的导游安排Cell
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                start = DateTime.Parse(DateTime.Parse(dr["StartDate"].ToString()).ToString("yyyy-MM-dd"));
                end = DateTime.Parse(DateTime.Parse(dr["EndDate"].ToString()).ToString("yyyy-MM-dd"));

                if (_StartDate == end)
                {
                    dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                    dr["EndDate"] = _StartDate.ToString("yyyy-MM-dd");
                }
                if (_StartDate > start && _EndDate > end)
                {
                    dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                    dr["EndDate"] = end.ToString("yyyy-MM-dd");
                }
                if (_StartDate < start && _EndDate > end)
                {
                    dr["StartDate"] = start.ToString("yyyy-MM-dd");
                    dr["EndDate"] = end.ToString("yyyy-MM-dd");
                }
                if (_StartDate < start && _EndDate < end)
                {
                    dr["StartDate"] = start.ToString("yyyy-MM-dd");
                    dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
                }
                if (start == _EndDate)
                {
                    dr["StartDate"] = start.ToString("yyyy-MM-dd");
                    dr["EndDate"] = start.ToString("yyyy-MM-dd");
                }
                if (_StartDate > start && _EndDate < end)
                {
                    dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                    dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
                }
                if (_StartDate == start && _EndDate == end)
                {
                    dr["StartDate"] = start.ToString("yyyy-MM-dd");
                    dr["EndDate"] = end.ToString("yyyy-MM-dd");
                }
            }
            return ds.Tables[0];
        }
        else
            return null;
    }
    /// <summary>
    /// 获取指定年和月的导游安排信息
    /// </summary>
    /// <param name="yearMonth">年和月的DateTime类型数据</param>
    /// <returns></returns>
    public static DataTable GetGuideArrangeData(DateTime yearMonth)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT [GuideArrangeID], [TourgroupID], [StartDate],[Calendars], [EndDate], [CiceroniID], [CiceroniName],
                                                [Notified],[IsAccompany], [HasEvent], [Remark] 
                                                FROM cic_GuideArrange 
                                                WHERE (datepart(year,[StartDate])={0} AND datepart(month,[StartDate])={1}) OR (datepart(year,[EndDate])={2} AND datepart(month,[EndDate])={3})", yearMonth.Year, yearMonth.Month, yearMonth.Year, yearMonth.Month);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    /// 根据给定的上下团时间取得未安排的导游
    /// </summary>
    /// <param name="startDateTime"></param>
    /// <param name="endDateTime"></param>
    /// <returns></returns>
    public static DataTable GetGuideArrangeData(string cityname, string language, int statusIndex, DateTime startDateTime, DateTime endDateTime)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = "";
        switch (statusIndex)
        {
            case 0://待安排
                sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid NOT IN 
                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
                                         AND datediff(minute,EndDate,'{0}')<=0) 
                                         OR (datediff(minute,StartDate,'{1}')>=0  
                                         AND datediff(minute,EndDate,'{1}')<=0) 
                                         OR (datediff(minute,StartDate,'{0}')<=0  
                                         AND datediff(minute,EndDate,'{1}')>=0)) ORDER BY CiceroniLevel Desc",
                                     startDateTime.ToString("yyyy-MM-dd HH:mm"),
                                     endDateTime.ToString("yyyy-MM-dd HH:mm"),
                                     cityname,
                                     language);
                break;
            case 1://已安排
                sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid IN 
                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
                                         AND datediff(minute,EndDate,'{0}')<=0) 
                                         OR (datediff(minute,StartDate,'{1}')>=0  
                                         AND datediff(minute,EndDate,'{1}')<=0) 
                                         OR (datediff(minute,StartDate,'{0}')<=0  
                                         AND datediff(minute,EndDate,'{1}')>=0))  ORDER BY CiceroniLevel Desc",
                                     startDateTime.ToString("yyyy-MM-dd HH:mm"),
                                     endDateTime.ToString("yyyy-MM-dd HH:mm"),
                                     cityname,
                                     language);
                break;
            case 2://全部
                sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{0}%' AND CiceroniLanguage like '%{1}%'  ORDER BY CiceroniLevel Desc", cityname, language);
                break;
            default:
                break;
        }

        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    /// 根据指定日期和导游ID获取导游安排表的日历Calendars内容和主键GuideArrangeID值
    /// </summary>
    /// <param name="yMd"></param>
    /// <param name="ciceroniId">导游ID</param>
    /// <returns></returns>
    public static DataTable GetGuideArrangeData(string yMd, string ciceroniId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format(@"SELECT [TourgroupID], [StartDate], [EndDate], [CiceroniName],
                                                [Notified], [HasEvent], [Remark] FROM cic_GuideArrange WHERE convert(varchar(10),[StartDate],121)<='{0}' AND '{1}'<=convert(varchar(10),[EndDate],121) AND  CiceroniID={2}", yMd, yMd, ciceroniId);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;

    }
    #endregion

}
